﻿using Lamex.Base;
using Lamex.Interface;
using Lamex.Library;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Lamex.mDashboard
{
    public partial class FrmTableBoardOutInStock : frmBaseChild
    {
        public FrmTableBoardOutInStock()
        {
            InitializeComponent();
            DateEditFormat();
        }

        private void FrmTableBoardOutInStock_Load(object sender, EventArgs e)
        {
            try { this.InitButtons(); } catch { }//初始化标准功能，因要应用于RibbonForm，所以加入异常处理
        }
        public void DateEditFormat()
        {
            this.dtStartTime.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
            this.dtStartTime.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtStartTime.Properties.EditFormat.FormatString = "yyyy-MM-dd";
            this.dtStartTime.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtStartTime.Properties.Mask.EditMask = "yyyy-MM-dd";

            this.dtEndTime.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
            this.dtEndTime.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtEndTime.Properties.EditFormat.FormatString = "yyyy-MM-dd";
            this.dtEndTime.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtEndTime.Properties.Mask.EditMask = "yyyy-MM-dd";

            DateTime dt = DateTime.Now;
            dtStartTime.Text = dt.AddMonths(-dt.Month + 1).AddDays(-dt.Day + 1).ToString("yyyy-MM-dd"); //本年第一天
            dtEndTime.Text = dt.ToString("yyyy-MM-dd");//当前日期
        }

        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        public override void DoView(Lamex.Interface.IButtonInfo sender)
        {
            //一定要选择时间
            if (string.IsNullOrEmpty(dtStartTime.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请选择开始时间", "提示", MessageBoxButtons.OK);
                return;
            }
            if (string.IsNullOrEmpty(dtEndTime.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请选择结束时间", "提示", MessageBoxButtons.OK);
                return;
            }
            if (string.IsNullOrEmpty(cbSelect.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请选择筛选条件", "提示", MessageBoxButtons.OK);
                return;
            }
            if (string.IsNullOrEmpty(cbType.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请选择库存类型", "提示", MessageBoxButtons.OK);
                return;
            }
            // gridControl1 = new DevExpress.XtraGrid.GridControl();
            gridView1.Columns.Clear();
            DataTable dt = GetData(cbSelect.Text, cbType.Text);
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
            this.gridView1.OptionsView.ColumnAutoWidth = false;
            base.DoView(sender);

        }
        //导出
        public override void DoExport(IButtonInfo sender)
        {
            try
            {
                SaveFileDialog dlg = new SaveFileDialog();
                //dlg.Filter = "PDF file(*.pdf)|*.pdf";
                dlg.Filter = "Excel文件(*.xls)|*.xls";
                DialogResult dialogResult = dlg.ShowDialog(this);
                if (dialogResult == DialogResult.OK)
                {
                    gridView1.ExportToXls(dlg.FileName);
                    DevExpress.XtraEditors.XtraMessageBox.Show("导出成功", "提示", MessageBoxButtons.OK);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public DataTable GetData(string SeletYMW, string Type)
        {
            string YMW = "  CONVERT(datetime,CONVERT(VARCHAR(10),Jahr)+'-'+ CONVERT(VARCHAR(10),Monat)+'-'+ CONVERT(VARCHAR(10),Tag))";
            string Type_Value = "";
            string MaterialNo = string.IsNullOrEmpty(txtMaterialNo.Text) ? "%%" : txtMaterialNo.Text;
            DataTable dt = new DataTable();
            DataTable dtNew = new DataTable();
            string sql = "";
            //类型转换
            switch (Type)
            {
                case "出库":
                    Type_Value = "2";
                    break;
                case "入库":
                    Type_Value = "5";
                    break;
            }

            switch (SeletYMW)
            {
                case "年":
                    sql = @"select MaterialID as '物料编号',Jahr, sum(Anzahl) as 'QTY' from [StorageManager].[dbo].[T3_Material_BDE] 
	WHERE MaterialID like '" + MaterialNo + "' and Ereignistyp='" + Type_Value + "' and " + YMW + ">='" + dtStartTime.Text + "' and " + YMW + "<='" + dtEndTime.Text + "' group by  MaterialID,Jahr";
                    dt = BaseBuildSql.GetDataTableBySql(sql);
                    dtNew = createTable(dt, "Jahr", "物料编号", "QTY");
                    break;
                case "月":
                    sql = @"select MaterialID as '物料编号',Monat, sum(Anzahl) as 'QTY' from [StorageManager].[dbo].[T3_Material_BDE] 
	WHERE MaterialID like '" + MaterialNo + "' and Ereignistyp='" + Type_Value + "' and " + YMW + ">='" + dtStartTime.Text + "' and " + YMW + "<='" + dtEndTime.Text + "' group by  MaterialID,Monat";
                    dt = BaseBuildSql.GetDataTableBySql(sql);
                    dtNew = createTable(dt, "Monat", "物料编号", "QTY");
                    break;
                case "周":
                    sql = @"select MaterialID as '物料编号',datepart(WEEK, CONVERT(VARCHAR(10),Jahr)+'-'+ CONVERT(VARCHAR(10),Monat)+'-'+ CONVERT(VARCHAR(10),Tag)) as 'week', sum(Anzahl) as 'QTY' from [StorageManager].[dbo].[T3_Material_BDE] 
	WHERE MaterialID like '" + MaterialNo + "' and Ereignistyp='" + Type_Value + "' and " + YMW + ">='" + dtStartTime.Text + "' and " + YMW + "<='" + dtEndTime.Text + "' group by  MaterialID,datepart(WEEK, CONVERT(VARCHAR(10),Jahr)+'-'+ CONVERT(VARCHAR(10),Monat)+'-'+ CONVERT(VARCHAR(10),Tag))";
                    dt = BaseBuildSql.GetDataTableBySql(sql);
                    dtNew = createTable(dt, "Week", "物料编号", "QTY");
                    break;
            }
            return dtNew;
        }



        /// <summary>
        /// 行转列
        /// </summary>
        /// <param name="dataTable">需要进行行转列的表</param>
        /// <param name="groupingColumn">需要进行行转列的字段</param>
        /// <param name="nameColumn">名称列</param>
        /// <param name="dataColumn">数据列</param>
        /// <returns></returns>
        private DataTable createTable(DataTable dataTable, string groupingColumn, string nameColumn, string dataColumn)
        {
            //取出不重复的需要转换的列的数据
            DataTable distinct_GroupingColumn = dataTable.DefaultView.ToTable(true, groupingColumn);
            //取出不重复的名称列
            DataTable dictinct_NameColumn = dataTable.DefaultView.ToTable(true, nameColumn);
            //构建新表
            DataTable table = new DataTable();
            #region 构建新表的列
            //将名称列添加进新表
            DataColumn newColumn = new DataColumn();
            newColumn.ColumnName = nameColumn;
            //添加列   所以要.Columns
            table.Columns.Add(newColumn);
            //添加需要转换的列            
            foreach (DataRow dr in distinct_GroupingColumn.Rows)
            {
                newColumn = new DataColumn();
                newColumn.ColumnName = dr[groupingColumn].ToString();
                table.Columns.Add(newColumn);
            }
            #endregion
            #region 向新表中添加数据
            DataRow newRow;
            DataRow[] dnewRow;
            foreach (DataRow item in dictinct_NameColumn.Rows)
            {
                //添加一个新行
                newRow = table.NewRow();
                //为此新行添加第一个行数据
                newRow[nameColumn] = item[nameColumn].ToString();
                //为此新行添加列数据
                foreach (DataRow r in distinct_GroupingColumn.Rows)
                {
                    dnewRow = dataTable.Select(nameColumn + " ='" + item[nameColumn].ToString() + "' and " + groupingColumn + " ='" + r[groupingColumn].ToString() + "'");
                    if (dnewRow.Length != 0)
                    {
                        //将数据与新建表进行连合
                        newRow[r[groupingColumn].ToString()] = dnewRow[0][dataColumn];
                    }
                }
                table.Rows.Add(newRow);
            }
            #endregion

            return table;
        }

     
    }
}
